import excel "macroprudential.xlsx", sheet("quarterly") cellrange(A1:D64) firstrow clear
gen date = yq(2007,1) + _n - 1
drop dateq

tempfile macroprudential
save `macroprudential'

import excel "macroprudential.xlsx", sheet("monthly") firstrow clear
drop datem
gen datem = ym(2007,1) + _n - 1
gen date = qofd(dofm(datem))
rename yearfixedrate fixedrate2
rename E fixedrate3
collapse hpigrowth mortgage fixedrate2 fixedrate3 Standard, by(date)
merge 1:1 date using `macroprudential', nogenerate

replace hpigrowth = hpigrowth * 100
gen lmortgage = log(mortgage)
replace LTVshare = 1-LTVshare

* LTVshare: share of residential loans with LTV greater than 75%
* LTIshare: share of residential loans with with income multiple >= 4 for singles and 3 for joint

format date %tq
tsset date

* IRF
var hpi LTV, lags(1/8) exog(l(0/4).fixedrate2 l(0/4).lmortgage)
irf create hpi, step(16) set(LTV, replace)
irf graph oirf, impulse(LTVshare) response(hpigrowth) xtitle("Quarters") ytitle("") subtitle("") byopts(title("IRF of house price to LTV share changes") note("") legend(off)) graphregion(color(white)) ylab(,nogrid) 
graph export "hpi_ltv.pdf", replace


var PE LTV, lags(1/8) exog(l(0/4).fixedrate2 l(0/4).lmortgage l(0/4).hpi)
irf create PE, step(16) set(LTV, replace)
irf graph oirf, impulse(LTVshare) response(PEratio) xtitle("Quarters") ytitle("") subtitle("") byopts(title("IRF of price-to-earning ratios to LTV share changes") note("") legend(off)) graphregion(color(white)) ylab(,nogrid) 
graph export "pe_ltv.pdf", replace


var hpi LTI, lags(1/8) exog(l(0/4).fixedrate3 l(0/4).lmortgage)
irf create hpi, step(16) set(LTI, replace)
irf graph oirf, impulse(LTIshare) response(hpigrowth) xtitle("Quarters") ytitle("") subtitle("") byopts(title("IRF of house price to LTI share changes") note("") legend(off)) graphregion(color(white)) ylab(,nogrid) 
graph export "hpi_lti.pdf", replace

var PE LTI, lags(1/8) exog(l(0/4).fixedrate3 l(0/4).lmortgage l(0/4).hpi)
irf create PE, step(16) set(LTI, replace)
irf graph oirf, impulse(LTIshare) response(PEratio) xtitle("Quatrers") ytitle("") subtitle("") byopts(title("IRF of price-to-earning ratios to LTI share changes") note("") legend(off)) graphregion(color(white)) ylab(,nogrid) 
graph export "pe_lti.pdf", replace
